﻿Imports ADOConnection
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient

Public Class CDoiTuong
    Protected _l As String
    Protected _kh As String
    Protected _ten As String
    Protected _nh As String

#Region "Property"
    Public Overridable Property L() As String
        Get
            Return _l
        End Get
        Set(ByVal value As String)
            _l = value
        End Set
    End Property

    Public Property KH() As String
        Get
            Return _kh
        End Get
        Set(ByVal value As String)
            _kh = value
        End Set
    End Property

    Public Property Ten() As String
        Get
            Return _ten
        End Get
        Set(ByVal value As String)
            _ten = value
        End Set
    End Property

    Public Property NH() As String
        Get
            Return _nh
        End Get
        Set(ByVal value As String)
            _nh = value
        End Set
    End Property

#End Region

    Public Function ColumnLength(ByVal column As String) As Integer
        Return CInt(BaseDB.ExecSql_DataValue("select COL_LENGTH('tblDMKHAC','" + column + "')")) / 2
    End Function

    Public Overrides Function ToString() As String
        Dim str As String
        If _l = "D" Or _l = "N" Or _l = "X" Then
            If _nh = "**" Or _kh.Trim().Length = 3 Then
                str = _nh + " " + _ten + "  -  " + _kh
            Else
                str = "   + " + _ten + "  -  " + _kh
            End If
        Else
            str = _ten + "  -  " + _kh
        End If
        Return str
    End Function

    Public Function IsChildExists() As Boolean
        Dim value As Boolean = False
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        Dim strSQL As String = "select * from tblDMKhac where"
        strSQL &= " l = " & objDBUtility.GetParamPlaceHolder("l", False)
        strSQL &= " and left(kh,len(" & objDBUtility.GetParamPlaceHolder("kh", False) & ")) = " & objDBUtility.GetParamPlaceHolder("kh", False)
        strSQL &= " and len(kh) > len(" & objDBUtility.GetParamPlaceHolder("kh", False) & ")"

        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)
        Dim objReader As CDataReader = Nothing
        Try
            With objCommand
                .Parameters.Add(objDBUtility.CreateParameter("l", DbType.String, _l, _l.Length))
                .Parameters.Add(objDBUtility.CreateParameter("kh", DbType.String, _kh, _kh.Length))
            End With
            objReader = objConnection.GetDataReader(objCommand, False)
            'Neu mat hang da ton tai thi lay _mathang_id va _code de hien thi tren Grid
            value = objReader.Read()
        Catch ex As System.Exception
            Throw ex
        Finally
            objConnection.Close()
        End Try
        Return value
    End Function

    Public Function IsExists() As Boolean
        Dim value As Boolean = False
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        Dim strSQL As String = "select * from tblDMKhac where"
        strSQL &= " l = " & objDBUtility.GetParamPlaceHolder("l", False)
        strSQL &= " and kh = " & objDBUtility.GetParamPlaceHolder("kh", False)

        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)
        Dim objReader As CDataReader = Nothing
        Try
            With objCommand
                .Parameters.Add(objDBUtility.CreateParameter("l", DbType.String, _l, _l.Length))
                .Parameters.Add(objDBUtility.CreateParameter("kh", DbType.String, _kh, _kh.Length))
            End With
            objReader = objConnection.GetDataReader(objCommand, False)
            'Neu mat hang da ton tai thi lay _mathang_id va _code de hien thi tren Grid
            value = objReader.Read()
        Catch ex As System.Exception
            Throw ex
        Finally
            objConnection.Close()
        End Try
        Return value
    End Function

    Public Function Insert() As Boolean
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        Dim objStringBuilder As New StringBuilder(250)
        With objStringBuilder
            .Append("INSERT INTO tblDMKhac(" & _
                    "l, kh, ten, nh" & _
                    ") VALUES(")
            .Append(objDBUtility.GetParamPlaceHolder("l", True))
            .Append(objDBUtility.GetParamPlaceHolder("kh", True))
            .Append(objDBUtility.GetParamPlaceHolder("ten", True))
            .Append(objDBUtility.GetParamPlaceHolder("nh", False))
            .Append(")")
        End With

        Dim sInsert As String = objStringBuilder.ToString

        Dim objCommand As IDbCommand = objConnection.CreateCommand(sInsert)
        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction

        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("l", DbType.String, _l, _l.Length))
                .Parameters.Add(objDBUtility.CreateParameter("kh", DbType.String, _kh, _kh.Length))
                .Parameters.Add(objDBUtility.CreateParameter("ten", DbType.String, _ten, _ten.Length))
                If _nh = Nothing Then
                    .Parameters.Add(objDBUtility.CreateParameter("nh", DbType.String, DBNull.Value, 0))
                Else
                    .Parameters.Add(objDBUtility.CreateParameter("nh", DbType.String, _nh, _nh.Length))
                End If
            End With

            objDBUtility.InsertAndReturnSerialKey(objCommand, "kh", "tblDMKhac")
            objTransaction.Commit()
        Catch ex As System.Exception
            objTransaction.Rollback()
            Throw ex
        Finally
            objConnection.Close()
        End Try
        Return True
    End Function

    Public Function Update() As Long
        Dim lRowsAffected As Long = 0
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        Dim strSQL As String = "UPDATE tblDMKhac SET"
        strSQL &= " ten = " & objDBUtility.GetParamPlaceHolder("ten", True)
        strSQL &= " nh = " & objDBUtility.GetParamPlaceHolder("nh", False)
        strSQL &= " WHERE kh = " & objDBUtility.GetParamPlaceHolder("kh", False)
        strSQL &= " and l = " & objDBUtility.GetParamPlaceHolder("l", False)

        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)
        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("ten", DbType.String, _ten, _ten.Length))
                If _nh = Nothing Then
                    .Parameters.Add(objDBUtility.CreateParameter("nh", DbType.String, DBNull.Value, 0))
                Else
                    .Parameters.Add(objDBUtility.CreateParameter("nh", DbType.String, _nh, _nh.Length))
                End If
                .Parameters.Add(objDBUtility.CreateParameter("kh", DbType.String, _kh, _kh.Length))
                .Parameters.Add(objDBUtility.CreateParameter("l", DbType.String, _l, _l.Length))
                lRowsAffected = .ExecuteNonQuery()
                objTransaction.Commit()
            End With
        Catch ex As System.Exception
            objTransaction.Rollback()
            Throw ex
        Finally
            objConnection.Close()
        End Try
        Return lRowsAffected
    End Function

    Public Function Delete() As Long
        Dim lRowsAffected As Long = 0
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        Dim strSQL As String = "delete from tblDMKhac where"
        strSQL &= " l = " & objDBUtility.GetParamPlaceHolder("l", False)
        strSQL &= " and kh = " & objDBUtility.GetParamPlaceHolder("kh", False)


        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)
        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("l", DbType.String, _l, _l.Length))
                .Parameters.Add(objDBUtility.CreateParameter("kh", DbType.String, _kh, _kh.Length))
                lRowsAffected = .ExecuteNonQuery()
                objTransaction.Commit()
            End With
        Catch ex As System.Exception
            objTransaction.Rollback()
            Throw ex
        Finally
            objConnection.Close()
        End Try
        Return lRowsAffected
    End Function
End Class

Public Class CDoiTuongs
    Public Shared Function GetNewDoiTuong_ID(ByVal loai As String, ByVal groupkh As String) As String
        Dim value As String
        Dim cmd As SqlCommand = Nothing
        Try
            Dim par(2) As SqlClient.SqlParameter
            par(0) = New SqlParameter("@groupkh", SqlDbType.NVarChar, 6)
            par(0).Value = groupkh.Trim()
            par(1) = New SqlParameter("@l", SqlDbType.VarChar, 1)
            par(1).Value = loai
            par(2) = New SqlParameter("@newkh", SqlDbType.NVarChar, 6)
            par(2).Direction = ParameterDirection.Output
            cmd = BaseDB.ExecSqlProcedure_Command("sp_GetNewDoiTuong_ID", par)
            value = cmd.Parameters("@newkh").Value
        Catch ex As Exception
            Throw ex
        Finally
            cmd.Connection.Close()
        End Try
        Return value
    End Function

    Public Shared Function GetDoiTuongByMa(ByVal loai As GeneralCode.enumDOITUONG, ByVal KH As String) As CDoiTuong
        Dim objDonVi As New CDoiTuong
        Dim strSQL As String
        strSQL = "SELECT L, TEN, NH" & _
                " FROM tblDMKhac" & _
                " WHERE KH = '" & KH & "'"
        strSQL &= " and L ='" + LocDoiTuong(loai) + "'"

        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection

        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)
        Dim objReader As CDataReader = Nothing

        Try
            objReader = objConnection.GetDataReader(objCommand, False)
            While objReader.Read
                With objDonVi
                    .L = objReader.GetString(0)
                    .Ten = objReader.GetString(1)
                    .NH = objReader.GetString(2)
                    .KH = KH
                End With
            End While
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
            objConnection.Close()
        Catch ex As System.Exception
            Throw ex
        End Try
        Return objDonVi
    End Function

    Public Shared Function GetDoiTuongByTen(ByVal loai As enumDOITUONG, ByVal Ten As String) As CDoiTuong
        Dim objDonVi As New CDoiTuong
        Dim strSQL As String

        strSQL = "SELECT *" & _
                " FROM tblDMKhac" & _
                " WHERE TEN = N'" & Ten & "'"
        strSQL &= " and L ='" + LocDoiTuong(loai) + "'"

        Dim objReader As SqlClient.SqlDataReader
        Try
            objReader = BaseDB.ExecSql_DataReader(strSQL)
            While objReader.Read
                With objDonVi
                    .L = objReader.GetString(0)
                    .KH = objReader.GetString(1)
                    .Ten = objReader.GetString(2)
                    .NH = BaseDB.GetObjectValue(objReader.GetValue(3))
                End With
            End While
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
            BaseDB.CloseConnect()
        Catch ex As System.Exception
            Throw ex
        End Try
        Return objDonVi
    End Function

    Public Shared Function GetDonVi_KhoListByStrDMKho(ByVal strDMKho As String) As ArrayList
        Dim aryKQ As New ArrayList
        Dim strSQL As String

        strSQL = "SELECT KH, TEN, NH" & _
                " FROM tblDMKhac" & strDMKho

        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection

        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)
        Dim objReader As CDataReader = Nothing

        Try
            objReader = objConnection.GetDataReader(objCommand, False)
            While objReader.Read
                Dim objItem As New CDoiTuong
                With objItem
                    .KH = objReader.GetString(0)
                    .Ten = objReader.GetString(1)
                    .NH = objReader.GetString(2)
                    .L = "K"
                End With
                aryKQ.Add(objItem)
            End While
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
            objConnection.Close()
        Catch ex As System.Exception
            Throw ex
        End Try
        Return aryKQ
    End Function

    Public Shared Function GetDoiTuongList(ByVal Loai As enumDOITUONG) As ArrayList
        Dim aryKQ As New ArrayList
        Dim strSQL As String
        strSQL = "SELECT *" & _
               " FROM tblDMKhac" & _
               " WHERE L = '" & LocDoiTuong(Loai) & "'"
        If Loai = enumDOITUONG.DVT Then
            strSQL += " order by ten"
        End If

        Dim objReader As SqlClient.SqlDataReader
        Try
            objReader = BaseDB.ExecSql_DataReader(strSQL)
            While objReader.Read
                Dim objItem As New CDoiTuong
                With objItem
                    .L = objReader.GetString(0)
                    .KH = objReader.GetString(1)
                    .Ten = objReader.GetString(2)
                    .NH = BaseDB.GetObjectValue(objReader.GetValue(3))
                End With
                aryKQ.Add(objItem)
            End While
            If objReader IsNot Nothing Then
                objReader.Close()
            End If
            BaseDB.CloseConnect()
        Catch ex As System.Exception
            Throw ex
        End Try
        Return aryKQ
    End Function

    Public Shared Function SearchDoiTuong(ByVal loai As GeneralCode.enumDOITUONG, ByVal strTen As String) As ArrayList
        Dim strSql As String = "select * from tblDMKhac where TEN like N'" + strTen + "' and L = '" + LocDoiTuong(loai) + "'"
        Dim aryKQ As New ArrayList
        Dim objReader As SqlClient.SqlDataReader
        Try
            objReader = BaseDB.ExecSql_DataReader(strSql)
            While objReader.Read
                Dim objItem As New CDoiTuong
                With objItem
                    .L = objReader.GetString(0)
                    .KH = objReader.GetString(1)
                    .Ten = objReader.GetString(2)
                    .NH = BaseDB.GetObjectValue(objReader.GetValue(3))
                End With
                aryKQ.Add(objItem)
            End While
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
            BaseDB.CloseConnect()
        Catch ex As System.Exception
            Throw ex
        End Try
        Return aryKQ
    End Function

    Public Shared Function GetDoiTuongListBySql(ByVal strSql As String) As ArrayList
        Dim aryKQ As New ArrayList
        Dim objReader As SqlClient.SqlDataReader
        Try
            objReader = BaseDB.ExecSql_DataReader(strSql)
            While objReader.Read
                Dim objItem As New CDoiTuong
                With objItem
                    .L = objReader.GetString(0)
                    .KH = objReader.GetString(1)
                    .Ten = objReader.GetString(2)
                    .NH = BaseDB.GetObjectValue(objReader.GetValue(3))
                End With
                aryKQ.Add(objItem)
            End While
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
            BaseDB.CloseConnect()
        Catch ex As System.Exception
            Throw ex
        End Try
        Return aryKQ
    End Function

    Public Shared Function GetNameByMa(ByVal loai As GeneralCode.enumDOITUONG, ByVal KH As String) As String
        Dim strSQL As String
        strSQL = "SELECT TEN FROM tblDMKhac" & _
                " WHERE KH = '" & KH & "'"
        strSQL &= " and L ='" + LocDoiTuong(loai) + "'"

        Dim ten As String = ""
        Try
            ten = BaseDB.ExecSql_DataValue(strSQL)
        Catch ex As System.Exception
            Throw ex
        End Try
        Return ten
    End Function

    Public Shared Function KiemTraTCNX(ByVal loai As enumTCNX, ByVal kh As String) As Boolean
        Dim strSQL As String
        strSQL = "SELECT * FROM tblTCNX WHERE KH = '" & kh & "' and TEN = N'" + LocTCNX(loai) + "'"
        Dim objReader As SqlClient.SqlDataReader
        Try
            objReader = BaseDB.ExecSql_DataReader(strSQL)
            If objReader.Read Then
                If Not IsNothing(objReader) Then
                    objReader.Close()
                End If
                BaseDB.CloseConnect()
                Return True
            End If
        Catch ex As System.Exception
            Throw ex
            Return False
        End Try
        Return False
    End Function
End Class
